﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SeDuce.IDAL;
using System.Data.SqlClient;

namespace SeDuce.DAL
{
    public class ProductDAL : IProductDAL
    {
        #region SQL
        public const string sqlGetProducts = "SELECT DISTINCT TbProduct.* FROM TbBrand INNER JOIN TbProdcutBrand_Map ON TbBrand.BrandID = TbProdcutBrand_Map.BrandID RIGHT OUTER JOIN TbProductType_Map INNER JOIN TbType ON TbProductType_Map.TypeID = TbType.TypeID RIGHT OUTER JOIN TbProduct ON TbProductType_Map.ProductID = TbProduct.ProductID ON TbProdcutBrand_Map.ProductID = TbProduct.ProductID WHERE (1 = 1)";
        public const string sqlGetProductsTop = "SELECT TOP ({0}) TbProduct.* FROM TbProduct";
        public const string sqlGetProductsByIDs = "SELECT TbProduct.* FROM TbProduct WHERE (TbProduct.ProductID IN ({0}))";
        public const string sqlGetProductByProductID = "SELECT TbProduct.* FROM TbProduct WHERE (ProductID = {0})";
        public const string sqlGetProductsByBrandID = "SELECT TbProduct.* FROM TbBrand INNER JOIN TbProdcutBrand_Map ON TbBrand.BrandID = TbProdcutBrand_Map.BrandID RIGHT OUTER JOIN TbProductType_Map INNER JOIN TbType ON TbProductType_Map.TypeID = TbType.TypeID RIGHT OUTER JOIN TbProduct ON TbProductType_Map.ProductID = TbProduct.ProductID ON TbProdcutBrand_Map.ProductID = TbProduct.ProductID WHERE (TbProdcutBrand_Map.BrandID = {0})";
        public const string sqlSearchProducts = "SELECT TbProduct.* FROM TbProduct WHERE CONTAINS((TbProduct.ProductName,TbProduct.Description), {0})";
        const string sqlUpdateProduct = "UPDATE TbProduct SET ProductName={0},UnitPrice={1},SalePrice={2},Size={3},Color={4},Description={5},Status={6},Date={7},ImageBig={8},Image174={9},Image100={10},UnitPriceUS={11},SalePriceUS={12},UnitPriceJP={13},SalePriceJP={14},Seq={15} WHERE (ProductID = {16})";
        const string sqlInsertProduct = "INSERT INTO TbProduct (ProductName,UnitPrice,SalePrice,Size,Color,Description,Status,Date,ImageBig,Image174,Image100,UnitPriceUS,SalePriceUS,UnitPriceJP,SalePriceJP,Seq) VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15});SELECT SCOPE_IDENTITY();";
        const string sqlDeleteProduct = "Delete from TbProduct where ProductID={0}";
        const string sqlInsertProductBrandMap = "INSERT INTO TbProdcutBrand_Map (ProductID, BrandID) VALUES ({0},{1})";
        const string sqlDeleteProductBrandMap = "Delete from TbProdcutBrand_Map where ProductID={0}";
        const string sqlInsertProductTypeMap = "INSERT INTO TbProductType_Map (ProductID, TypeID) VALUES ({0},{1})";
        const string sqlDeleteProductTypeMap = "Delete from TbProductType_Map where ProductID={0}";
        const string sqlGetBrandsByProductID = "SELECT TbBrand.BrandName, TbBrand.BrandID FROM TbBrand INNER JOIN TbProdcutBrand_Map ON TbBrand.BrandID = TbProdcutBrand_Map.BrandID WHERE (TbProdcutBrand_Map.ProductID = {0})";
        const string sqlGetTypesByProductID = "SELECT TbType.TypeID, TbType.TypeName FROM TbProductType_Map INNER JOIN TbType ON TbProductType_Map.TypeID = TbType.TypeID WHERE (TbProductType_Map.ProductID = {0})";


        #endregion

        #region Variables
        private static DataAccessObjectBase _dao = null;
        public static DataAccessObjectBase DAO
        {
            get
            {
                if (_dao == null)
                    _dao = new DataAccessObjectBase();
                return _dao;
            }
            set
            {
                _dao = value;
            }
        }
        #endregion

        public System.Data.DataTable GetProducts()
        {
            return DAO.Select(sqlGetProducts);
        }

        public System.Data.DataTable GetProductsTop(int num)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@num", num)
            };
            string sql = string.Format(sqlGetProductsTop, "@num");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetProductByProductID(int productID)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ProductID", productID)
            };
            string sql = string.Format(sqlGetProductByProductID, "@ProductID");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetBrandsByProductID(int productID)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ProductID", productID)
            };
            string sql = string.Format(sqlGetBrandsByProductID, "@ProductID");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetTypesByProductID(int productID)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ProductID", productID)
            };
            string sql = string.Format(sqlGetTypesByProductID, "@ProductID");
            return DAO.Select(sql, sp);
        }

        public System.Data.DataTable GetProductsByIDs(string IDs)
        {
            string sql = string.Format(sqlGetProductsByIDs, IDs);
            return DAO.Select(sql);
        }

        public bool UpdateProduct(Model.Product product, List<int> brandIDs, List<int> typeIDs)
        {
            Dictionary<SqlParameter[], string> updateSqls = new Dictionary<SqlParameter[], string>();

            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ProductName", product.ProductName),
                new SqlParameter ("@UnitPrice", product.UnitPrice),
                new SqlParameter ("@SalePrice", product.SalePrice),
                new SqlParameter ("@Size", product.Size),
                new SqlParameter ("@Color", product.Color),
                new SqlParameter ("@Description", product.Description),
                new SqlParameter ("@Status", product.Status),
                new SqlParameter ("@Date", product.Date),
                new SqlParameter ("@ImageBig", product.ImageBig),
                new SqlParameter ("@Image174", product.Image174),
                new SqlParameter ("@Image100", product.Image100),
                new SqlParameter ("@UnitPriceUS", product.UnitPriceUS),
                new SqlParameter ("@SalePriceUS", product.SalePriceUS),
                new SqlParameter ("@UnitPriceJP", product.UnitPriceJP),
                new SqlParameter ("@SalePriceJP", product.SalePriceJP),
                new SqlParameter ("@Seq", product.Seq),
                new SqlParameter ("@ProductID", product.ProductID)
            };
            string sql = string.Format(sqlUpdateProduct, "@ProductName", "@UnitPrice", "@SalePrice", "@Size", "@Color", "@Description", "@Status", "@Date", "@ImageBig", "@Image174", "@Image100", "@UnitPriceUS", "@SalePriceUS", "@UnitPriceJP", "@SalePriceJP", "@Seq", "@ProductID");
            updateSqls.Add(sp, sql);

            if (brandIDs != null)
            {
                SqlParameter[] sp2 = new SqlParameter[] { 
                    new SqlParameter ("@ProductID",product.ProductID),
                };
                string sql2 = string.Format(sqlDeleteProductBrandMap, "@ProductID");
                updateSqls.Add(sp2, sql2);


                for (int i = 0; i < brandIDs.Count; i++)
                {
                    SqlParameter[] sp3 = new SqlParameter[] { 
                        new SqlParameter ("@ProductID",product.ProductID),
                        new SqlParameter ("@BrandID",brandIDs[i])
                      };
                    string sql3 = string.Format(sqlInsertProductBrandMap, "@ProductID", "@BrandID");
                    updateSqls.Add(sp3, sql3);
                }
            }

            if (typeIDs != null)
            {
                SqlParameter[] sp4 = new SqlParameter[] { 
                    new SqlParameter ("@ProductID",product.ProductID),
                };
                string sql4 = string.Format(sqlDeleteProductTypeMap, "@ProductID");
                updateSqls.Add(sp4, sql4);


                for (int i = 0; i < typeIDs.Count; i++)
                {
                    SqlParameter[] sp5 = new SqlParameter[] { 
                        new SqlParameter ("@ProductID",product.ProductID),
                        new SqlParameter ("@TypeID",typeIDs[i])
                      };
                    string sql5 = string.Format(sqlInsertProductTypeMap, "@ProductID", "@TypeID");
                    updateSqls.Add(sp5, sql5);
                }
            }

            return DAO.ExecuteSqlTran(updateSqls);
        }

        public object InsertProduct(Model.Product product, List<int> brandIDs, List<int> typeIDs)
        {
            int productID = 0;
            Dictionary<SqlParameter[], string> insertSqls = new Dictionary<SqlParameter[], string>();
            
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter ("@ProductName", product.ProductName),
                new SqlParameter ("@UnitPrice", product.UnitPrice),
                new SqlParameter ("@SalePrice", product.SalePrice),
                new SqlParameter ("@Size", product.Size),
                new SqlParameter ("@Color", product.Color),
                new SqlParameter ("@Description", product.Description),
                new SqlParameter ("@Status", product.Status),
                new SqlParameter ("@Date", product.Date),
                new SqlParameter ("@ImageBig", product.ImageBig),
                new SqlParameter ("@Image174", product.Image174),
                new SqlParameter ("@Image100", product.Image100),
                new SqlParameter ("@UnitPriceUS", product.UnitPriceUS),
                new SqlParameter ("@SalePriceUS", product.SalePriceUS),
                new SqlParameter ("@UnitPriceJP", product.UnitPriceJP),
                new SqlParameter ("@SalePriceJP", product.SalePriceJP),
                new SqlParameter ("@Seq", product.Seq)
            };
            string sql = string.Format(sqlInsertProduct, "@ProductName", "@UnitPrice", "@SalePrice", "@Size", "@Color", "@Description", "@Status", "@Date", "@ImageBig", "@Image174", "@Image100", "@UnitPriceUS", "@SalePriceUS", "@UnitPriceJP", "@SalePriceJP", "@Seq");
            object obj = DAO.GetSingle(sql, sp);
            if (obj != null)
            {
                productID = Convert.ToInt32(obj);

                for (int i = 0; i < brandIDs.Count; i++)
                {
                    SqlParameter[] parms = new SqlParameter[] { 
                        new SqlParameter ("@ProductID",productID),
                        new SqlParameter ("@BrandID",brandIDs[i])
                      };
                    string sql2 = string.Format(sqlInsertProductBrandMap, "@ProductID", "@BrandID");
                    insertSqls.Add(parms, sql2);
                }

                for (int i = 0; i < typeIDs.Count; i++)
                {
                    SqlParameter[] parms2 = new SqlParameter[] { 
                        new SqlParameter ("@ProductID",productID),
                        new SqlParameter ("@TypeID",typeIDs[i])
                      };
                    string sql3 = string.Format(sqlInsertProductTypeMap, "@ProductID", "@TypeID");
                    insertSqls.Add(parms2, sql3);
                }
            }
            if (DAO.ExecuteSqlTran(insertSqls))
            {
                return obj;
            }
            else
            {
                return null;
            }
        }

        public bool DeleteProduct(int productID)
        {
            Dictionary<SqlParameter[], string> deleteSqls = new Dictionary<SqlParameter[], string>();

            SqlParameter[] sp = new SqlParameter[] { 
                new SqlParameter ("@ProductID",productID),
            };
            string sql = string.Format(sqlDeleteProduct, "@ProductID");
            deleteSqls.Add(sp, sql);

            SqlParameter[] sp2 = new SqlParameter[] { 
                new SqlParameter ("@ProductID",productID),
            };
            string sql2 = string.Format(sqlDeleteProductBrandMap, "@ProductID");
            deleteSqls.Add(sp2, sql2);

            SqlParameter[] sp3 = new SqlParameter[] { 
                new SqlParameter ("@ProductID",productID),
            };
            string sql3 = string.Format(sqlDeleteProductTypeMap, "@ProductID");
            deleteSqls.Add(sp3, sql3);

            return DAO.ExecuteSqlTran(deleteSqls);
        }
    }
}
